﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.IO;
using WatiN.Core;
using WebAutomation.Browsers;

namespace WebAutomation
{
    public class DataAdapterCommand : CommandBase
    {
        public DataAdapterCommand(WebBrowserObject browserObject)
        {
            this.Initialize(browserObject);

        }
        public override bool ExecuteCommand(WebBrowserObject browserObject, string[] parameter, string url, string testRepo, string testResultLocation, string dbConnString, ref DataSet dataFromDB, ref TestLineResult testLineResult, ref bool stopTest, WebAutoUtils.ITestDataExceptionRules testDataExceptionRules)
        {
            DataSet ds = new DataSet();
            string command = parameter[0];
            testLineResult.LineCommand = command;
            testLineResult.LineParameter = parameter[1];
            if (!string.IsNullOrEmpty(testLineResult.LineParameter))
            {
                if(!testLineResult.LineParameter.Contains("],"))
                {
                    testLineResult.LineParameter = testLineResult.LineParameter.Replace(",", "],");
                }
                //File value parameter
                if (testLineResult.LineParameter.ToLower().Contains(@":\"))
                {
                    if(!testLineResult.LineParameter.EndsWith("]"))
                    {
                        testLineResult.LineParameter = testLineResult.LineParameter + "]";
                    }
                }
                else
                {
                    if (!testLineResult.LineParameter.Contains("]:"))
                    {
                        testLineResult.LineParameter = testLineResult.LineParameter.Replace(":", "]:");
                    }
                }
            }

            if (this.BrowserObject == null)
            {
                this.BrowserObject = browserObject;
            }

            parameter[1] = parameter[1].Replace(@":\", @"~\");

            string[] array = parameter[1].Split(':');
            string dsName = "";
            string sqlCmdFileOrString = array[0];
            string[] sqlCmdFileOrStringParams =null;
            string[] sqlCmdFileOrStringParamsCopy = null;
            if(array[1].Contains(","))
            {
                if (array[1].Contains("Variable"))
                {
                    sqlCmdFileOrStringParams = (array[1]).Split(',');
                }
                else
                {
                    sqlCmdFileOrStringParams = array[1].Replace("[", "").Replace("]", "").Replace("!",":").Split(',');
                }
            }else
            {
                if (array[1].Contains("Variable"))
                {
                    sqlCmdFileOrStringParams = (array[1] + ",").Split(',');
                }
                else
                {
                    sqlCmdFileOrStringParams = (array[1].Replace("[", "").Replace("]", "").Replace("!", ":") + ",").Split(',');
                }
            }

           
            
            string sql = GetSqlToRun(testRepo, sqlCmdFileOrString, sqlCmdFileOrStringParams);
//            ds.DataSetName = sqlCmdFileOrString;
            testLineResult.LineSubCommand = command + ":" + sql;
            try
            {
                dsName = array[2].ToString();
            }catch
            {
                throw new Exception(
                    "The Dataset name was not passed in.Make sure that you specifiy the dataset name eg. Data?Exec|Check_PolicyNo.sql:[#PolicyNo='Variable[randPolicyNo]']:dsName1>");
            }
            try
            {
                if (string.IsNullOrEmpty(sql))
                {
                    testLineResult.LineMessage = "Error.Could not get the T-SQL to run.";
                    testLineResult.LinePassed = false;
                }
                else
                {

                    switch (command.ToLower())
                    {
                        case "exec":

                            ds = ExecuteSql(dbConnString, sql, dsName);
                            testLineResult.LinePassed = true;
                            break;

                    }
                }

                dataFromDB = ds;

            }catch(Exception ex)
            {
                testLineResult.IsFrameworkError = true;
                testLineResult.LineMessage = "Error." + ex.Message+"."+ex.StackTrace + ".\nSql:" + sql;
                testLineResult.LinePassed = false;
            }
            return testLineResult.LinePassed;
        }

        public virtual DataSet ExecuteSql(string dbConnectionString, string sqlToRun,string dataSetName)
        {
            string dsName = dataSetName;
            
            DataSet ds = new DataSet(dsName);
            using (var conn = new SqlConnection(dbConnectionString))
            {
                conn.Open();
                var sp = new SqlCommand(sqlToRun);
                sp.Connection = conn;
                SqlDataAdapter adp = new SqlDataAdapter(sp);

                adp.Fill(ds);
            }

            return ds;

        }

        public virtual string GetSqlToRun(string testRepo,string sqlCmdFileOrString, string[] sqlCmdFileOrStringParams)
        {
            string returnValue = "";
            bool isFile = false;
            string filePath = testRepo + "\\" + sqlCmdFileOrString;
            if(File.Exists(filePath))
            {
                isFile = true;
            }

            if(isFile)
            {
                String line = "";
                using (StreamReader sr = new StreamReader(filePath))
                {
                    while ((line = sr.ReadLine()) != null)
                    {
                        returnValue += line+Environment.NewLine;
                    }

                    sr.Close();
                }
            }else
            {
                returnValue = sqlCmdFileOrString;

            }
            returnValue = returnValue.Replace("GO", "\r\n").Replace("go", "\r\n");

            string name = "";
            string value = "";
            foreach (var item in sqlCmdFileOrStringParams)
            {
                try
                {
                    name = item.Split('=')[0].Replace("[","").Replace("]","");
                    value = item.Split('=')[1];
                    if (value.Contains("Variable"))
                    {
                        string varName = value.Replace("Variable[", "").Replace("]", "");
                        varName = varName.Replace("Variable", "");
                        string varValue = TestCommands.GetVariable(varName);
                        value = value.Replace("Variable[", varValue).Replace("]", "").Replace(varName.Replace("'",""), "");
                    }
                    returnValue = returnValue.Replace(name, value);
                }
                catch { }

            }

            returnValue = returnValue.Replace(@"~\", @":\");

            return returnValue;

        }

    }
}
